﻿/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2012                    */
/* Created on:     30/05/2014 1:55:06 CH                        */
/*==============================================================*/
create database QuanLyBongDa
go
use QuanLyBongDa
go


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BXH') and o.name = 'FK_BXH_COHANG_CLB')
alter table BXH
   drop constraint FK_BXH_COHANG_CLB
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CAPDAU') and o.name = 'FK_CAPDAU_THUOC2_VONGDAU')
alter table CAPDAU
   drop constraint FK_CAPDAU_THUOC2_VONGDAU
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CAPDAU') and o.name = 'FK_CAPDAU_TOCHUC_SANVD')
alter table CAPDAU
   drop constraint FK_CAPDAU_TOCHUC_SANVD
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CAUTHU') and o.name = 'FK_CAUTHU_CHOI_VT_CT')
alter table CAUTHU
   drop constraint FK_CAUTHU_CHOI_VT_CT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CAUTHU') and o.name = 'FK_CAUTHU_THUOC_CLB')
alter table CAUTHU
   drop constraint FK_CAUTHU_THUOC_CLB
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CLB') and o.name = 'FK_CLB_CO_HLV')
alter table CLB
   drop constraint FK_CLB_CO_HLV
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CLB') and o.name = 'FK_CLB_CO2_SANVD')
alter table CLB
   drop constraint FK_CLB_CO2_SANVD
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CLB') and o.name = 'FK_CLB_QUANLY_BTC')
alter table CLB
   drop constraint FK_CLB_QUANLY_BTC
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CLB') and o.name = 'FK_CLB_TAITRO_CL_NTT')
alter table CLB
   drop constraint FK_CLB_TAITRO_CL_NTT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CO3') and o.name = 'FK_CO3_CO3_CLB')
alter table CO3
   drop constraint FK_CO3_CO3_CLB
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CO3') and o.name = 'FK_CO3_CO4_TONGKET')
alter table CO3
   drop constraint FK_CO3_CO4_TONGKET
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('DIEUKHIEN') and o.name = 'FK_DIEUKHIE_DIEUKHIEN_TRONGTAI')
alter table DIEUKHIEN
   drop constraint FK_DIEUKHIE_DIEUKHIEN_TRONGTAI
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('DIEUKHIEN') and o.name = 'FK_DIEUKHIE_DIEUKHIEN_CAPDAU')
alter table DIEUKHIEN
   drop constraint FK_DIEUKHIE_DIEUKHIEN_CAPDAU
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('THIDAU') and o.name = 'FK_THIDAU_THIDAU_CAPDAU')
alter table THIDAU
   drop constraint FK_THIDAU_THIDAU_CAPDAU
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('THIDAU') and o.name = 'FK_THIDAU_THIDAU2_CLB')
alter table THIDAU
   drop constraint FK_THIDAU_THIDAU2_CLB
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TRONGTAI') and o.name = 'FK_TRONGTAI_PHANCONG_BTC')
alter table TRONGTAI
   drop constraint FK_TRONGTAI_PHANCONG_BTC
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TRONGTAI') and o.name = 'FK_TRONGTAI_THUOC3_VT_TT')
alter table TRONGTAI
   drop constraint FK_TRONGTAI_THUOC3_VT_TT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('VONGDAU') and o.name = 'FK_VONGDAU_SAPXEP_BTC')
alter table VONGDAU
   drop constraint FK_VONGDAU_SAPXEP_BTC
go

if exists (select 1
            from  sysobjects
           where  id = object_id('BTC')
            and   type = 'U')
   drop table BTC
go

if exists (select 1
            from  sysobjects
           where  id = object_id('BXH')
            and   type = 'U')
   drop table BXH
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('CAPDAU')
            and   name  = 'TOCHUC_FK'
            and   indid > 0
            and   indid < 255)
   drop index CAPDAU.TOCHUC_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('CAPDAU')
            and   name  = 'THUOC2_FK'
            and   indid > 0
            and   indid < 255)
   drop index CAPDAU.THUOC2_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CAPDAU')
            and   type = 'U')
   drop table CAPDAU
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('CAUTHU')
            and   name  = 'CHOI_FK'
            and   indid > 0
            and   indid < 255)
   drop index CAUTHU.CHOI_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('CAUTHU')
            and   name  = 'THUOC_FK'
            and   indid > 0
            and   indid < 255)
   drop index CAUTHU.THUOC_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CAUTHU')
            and   type = 'U')
   drop table CAUTHU
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('CLB')
            and   name  = 'TAITRO_CLB_FK'
            and   indid > 0
            and   indid < 255)
   drop index CLB.TAITRO_CLB_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('CLB')
            and   name  = 'CO2_FK'
            and   indid > 0
            and   indid < 255)
   drop index CLB.CO2_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('CLB')
            and   name  = 'CO_FK'
            and   indid > 0
            and   indid < 255)
   drop index CLB.CO_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CLB')
            and   type = 'U')
   drop table CLB
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('CO3')
            and   name  = 'CO3_FK'
            and   indid > 0
            and   indid < 255)
   drop index CO3.CO3_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('CO3')
            and   name  = 'CO4_FK'
            and   indid > 0
            and   indid < 255)
   drop index CO3.CO4_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CO3')
            and   type = 'U')
   drop table CO3
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('DIEUKHIEN')
            and   name  = 'DIEUKHIEN_FK'
            and   indid > 0
            and   indid < 255)
   drop index DIEUKHIEN.DIEUKHIEN_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('DIEUKHIEN')
            and   name  = 'DIEUKHIEN2_FK'
            and   indid > 0
            and   indid < 255)
   drop index DIEUKHIEN.DIEUKHIEN2_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('DIEUKHIEN')
            and   type = 'U')
   drop table DIEUKHIEN
go

if exists (select 1
            from  sysobjects
           where  id = object_id('HLV')
            and   type = 'U')
   drop table HLV
go

if exists (select 1
            from  sysobjects
           where  id = object_id('NTT')
            and   type = 'U')
   drop table NTT
go

if exists (select 1
            from  sysobjects
           where  id = object_id('SANVD')
            and   type = 'U')
   drop table SANVD
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('THIDAU')
            and   name  = 'THIDAU_FK'
            and   indid > 0
            and   indid < 255)
   drop index THIDAU.THIDAU_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('THIDAU')
            and   name  = 'THIDAU2_FK'
            and   indid > 0
            and   indid < 255)
   drop index THIDAU.THIDAU2_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('THIDAU')
            and   type = 'U')
   drop table THIDAU
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TONGKET')
            and   type = 'U')
   drop table TONGKET
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('TRONGTAI')
            and   name  = 'PHANCONG_FK'
            and   indid > 0
            and   indid < 255)
   drop index TRONGTAI.PHANCONG_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('TRONGTAI')
            and   name  = 'THUOC3_FK'
            and   indid > 0
            and   indid < 255)
   drop index TRONGTAI.THUOC3_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TRONGTAI')
            and   type = 'U')
   drop table TRONGTAI
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('VONGDAU')
            and   name  = 'SAPXEP_FK'
            and   indid > 0
            and   indid < 255)
   drop index VONGDAU.SAPXEP_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('VONGDAU')
            and   type = 'U')
   drop table VONGDAU
go

if exists (select 1
            from  sysobjects
           where  id = object_id('VT_CT')
            and   type = 'U')
   drop table VT_CT
go

if exists (select 1
            from  sysobjects
           where  id = object_id('VT_TT')
            and   type = 'U')
   drop table VT_TT
go

/*==============================================================*/
/* Table: BTC                                                   */
/*==============================================================*/
create table BTC (
   Ma_BTC               varchar(20)          not null,
   Ten_BTC              nvarchar(60)         null,
   Nam                  nvarchar(12)         null,
   NKy                  nvarchar(12)         null,
   NVu                  nvarchar(60)         null,
   MKhau                varchar(60)          null,
   constraint PK_BTC primary key nonclustered (Ma_BTC)
)
go

/*==============================================================*/
/* Table: BXH                                                   */
/*==============================================================*/
create table BXH (
   Ma_CLB               varchar(20)          not null,
   Hang                 varchar(20)          null,
   Thang                int                  null,
   Hoa                  int                  null,
   Thua                 int                  null,
   DiemTong             int                  null
)
go

/*==============================================================*/
/* Table: CAPDAU                                                */
/*==============================================================*/
create table CAPDAU (
   Ma_CD                varchar(20)          not null,
   ThoiGian             datetime             not null,
   Ma_VD                varchar(20)          not null,
   MaSan                varchar(20)          not null,
   DoiKhach             nvarchar(20)         null,
   DoiNha               nvarchar(20)         null,
   Tyso                 nvarchar(20)         null,
   KQ_DK                int                  null,
   KQ_DN                int                  null,
   constraint PK_CAPDAU primary key nonclustered (Ma_CD, ThoiGian)
)
go

/*==============================================================*/
/* Index: THUOC2_FK                                             */
/*==============================================================*/
create index THUOC2_FK on CAPDAU (
Ma_VD ASC
)
go

/*==============================================================*/
/* Index: TOCHUC_FK                                             */
/*==============================================================*/
create index TOCHUC_FK on CAPDAU (
MaSan ASC
)
go

/*==============================================================*/
/* Table: CAUTHU                                                */
/*==============================================================*/
create table CAUTHU (
   Ma_CT                varchar(20)          not null,
   Ma_CLB               varchar(20)          not null,
   Ma_VTCT              varchar(20)          not null,
   Ten_CT               nvarchar(60)         null,
   NgaySinh             datetime             null,
   Qtich                nvarchar(20)         null,
   DiaChi               nvarchar(60)         null,
   constraint PK_CAUTHU primary key nonclustered (Ma_CT)
)
go

/*==============================================================*/
/* Index: THUOC_FK                                              */
/*==============================================================*/
create index THUOC_FK on CAUTHU (
Ma_CLB ASC
)
go

/*==============================================================*/
/* Index: CHOI_FK                                               */
/*==============================================================*/
create index CHOI_FK on CAUTHU (
Ma_VTCT ASC
)
go

/*==============================================================*/
/* Table: CLB                                                   */
/*==============================================================*/
create table CLB (
   Ma_CLB               varchar(20)          not null,
   Ma_BTC               varchar(20)          not null,
   Ma_HLV               varchar(20)          not null,
   MaSan                varchar(20)          not null,
   Ma_NTT               varchar(20)          not null,
   Ten_CLB              nvarchar(60)         null,
   SoCauThu             int                  null,
   DPhuc                nvarchar(20)         null,
   constraint PK_CLB primary key nonclustered (Ma_CLB)
)
go

/*==============================================================*/
/* Index: CO_FK                                                 */
/*==============================================================*/
create index CO_FK on CLB (
Ma_HLV ASC
)
go

/*==============================================================*/
/* Index: CO2_FK                                                */
/*==============================================================*/
create index CO2_FK on CLB (
MaSan ASC
)
go

/*==============================================================*/
/* Index: TAITRO_CLB_FK                                         */
/*==============================================================*/
create index TAITRO_CLB_FK on CLB (
Ma_NTT ASC
)
go

/*==============================================================*/
/* Table: CO3                                                   */
/*==============================================================*/
create table CO3 (
   Ma_CLB               varchar(20)          not null,
   MaDH                 varchar(20)          not null,
   constraint PK_CO3 primary key nonclustered (Ma_CLB, MaDH)
)
go

/*==============================================================*/
/* Index: CO4_FK                                                */
/*==============================================================*/
create index CO4_FK on CO3 (
MaDH ASC
)
go

/*==============================================================*/
/* Index: CO3_FK                                                */
/*==============================================================*/
create index CO3_FK on CO3 (
Ma_CLB ASC
)
go

/*==============================================================*/
/* Table: DIEUKHIEN                                             */
/*==============================================================*/
create table DIEUKHIEN (
   Ma_TT                varchar(20)          not null,
   Ma_CD                varchar(20)          not null,
   ThoiGian             datetime             not null,
   constraint PK_DIEUKHIEN primary key nonclustered (Ma_TT, Ma_CD, ThoiGian)
)
go

/*==============================================================*/
/* Index: DIEUKHIEN2_FK                                         */
/*==============================================================*/
create index DIEUKHIEN2_FK on DIEUKHIEN (
Ma_CD ASC,
ThoiGian ASC
)
go

/*==============================================================*/
/* Index: DIEUKHIEN_FK                                          */
/*==============================================================*/
create index DIEUKHIEN_FK on DIEUKHIEN (
Ma_TT ASC
)
go

/*==============================================================*/
/* Table: HLV                                                   */
/*==============================================================*/
create table HLV (
   Ma_HLV               varchar(20)          not null,
   Ten_HLV              nvarchar(60)         null,
   NgaySinh             datetime             null,
   Qtich                nvarchar(20)         null,
   constraint PK_HLV primary key nonclustered (Ma_HLV)
)
go

/*==============================================================*/
/* Table: NTT                                                   */
/*==============================================================*/
create table NTT (
   Ma_NTT               varchar(20)          not null,
   Ten_NTT              nvarchar(60)         null,
   DChi                 nvarchar(60)         null,
   QCao                 nvarchar(60)         null,
   HD                   nvarchar(12)         null,
   constraint PK_NTT primary key nonclustered (Ma_NTT)
)
go

/*==============================================================*/
/* Table: SANVD                                                 */
/*==============================================================*/
create table SANVD (
   MaSan                varchar(20)          not null,
   TenSan               nvarchar(60)         null,
   SucChua              bigint               null,
   DD_San               nvarchar(60)         null,
   constraint PK_SANVD primary key nonclustered (MaSan)
)
go

/*==============================================================*/
/* Table: THIDAU                                                */
/*==============================================================*/
create table THIDAU (
   Ma_CD                varchar(20)          not null,
   ThoiGian             datetime             not null,
   Ma_CLB               varchar(20)          not null,
   constraint PK_THIDAU primary key nonclustered (Ma_CD, ThoiGian, Ma_CLB)
)
go

/*==============================================================*/
/* Index: THIDAU2_FK                                            */
/*==============================================================*/
create index THIDAU2_FK on THIDAU (
Ma_CLB ASC
)
go

/*==============================================================*/
/* Index: THIDAU_FK                                             */
/*==============================================================*/
create index THIDAU_FK on THIDAU (
Ma_CD ASC,
ThoiGian ASC
)
go

/*==============================================================*/
/* Table: TONGKET                                               */
/*==============================================================*/
create table TONGKET (
   MaDH                 varchar(20)          not null,
   TenDH                nvarchar(60)         null,
   NgayTraoDH           datetime             null,
   constraint PK_TONGKET primary key nonclustered (MaDH)
)
go

/*==============================================================*/
/* Table: TRONGTAI                                              */
/*==============================================================*/
create table TRONGTAI (
   Ma_TT                varchar(20)          not null,
   Ma_VTTT              varchar(20)          not null,
   Ma_BTC               varchar(20)          null,
   Ten_TT               nvarchar(60)         null,
   constraint PK_TRONGTAI primary key nonclustered (Ma_TT)
)
go

/*==============================================================*/
/* Index: THUOC3_FK                                             */
/*==============================================================*/
create index THUOC3_FK on TRONGTAI (
Ma_VTTT ASC
)
go

/*==============================================================*/
/* Index: PHANCONG_FK                                           */
/*==============================================================*/
create index PHANCONG_FK on TRONGTAI (
Ma_BTC ASC
)
go

/*==============================================================*/
/* Table: VONGDAU                                               */
/*==============================================================*/
create table VONGDAU (
   Ma_VD                varchar(20)          not null,
   Ma_BTC               varchar(20)          not null,
   Ten_VD               nvarchar(60)         null,
   constraint PK_VONGDAU primary key nonclustered (Ma_VD)
)
go

/*==============================================================*/
/* Index: SAPXEP_FK                                             */
/*==============================================================*/
create index SAPXEP_FK on VONGDAU (
Ma_BTC ASC
)
go

/*==============================================================*/
/* Table: VT_CT                                                 */
/*==============================================================*/
create table VT_CT (
   Ma_VTCT              varchar(20)          not null,
   Ten_VTCT             nvarchar(60)         null,
   constraint PK_VT_CT primary key nonclustered (Ma_VTCT)
)
go

/*==============================================================*/
/* Table: VT_TT                                                 */
/*==============================================================*/
create table VT_TT (
   Ma_VTTT              varchar(20)          not null,
   Ten_VTTT             nvarchar(60)         null,
   constraint PK_VT_TT primary key nonclustered (Ma_VTTT)
)
go

alter table BXH
   add constraint FK_BXH_COHANG_CLB foreign key (Ma_CLB)
      references CLB (Ma_CLB)
go

alter table CAPDAU
   add constraint FK_CAPDAU_THUOC2_VONGDAU foreign key (Ma_VD)
      references VONGDAU (Ma_VD)
go

alter table CAPDAU
   add constraint FK_CAPDAU_TOCHUC_SANVD foreign key (MaSan)
      references SANVD (MaSan)
go

alter table CAUTHU
   add constraint FK_CAUTHU_CHOI_VT_CT foreign key (Ma_VTCT)
      references VT_CT (Ma_VTCT)
go

alter table CAUTHU
   add constraint FK_CAUTHU_THUOC_CLB foreign key (Ma_CLB)
      references CLB (Ma_CLB)
go

alter table CLB
   add constraint FK_CLB_CO_HLV foreign key (Ma_HLV)
      references HLV (Ma_HLV)
go

alter table CLB
   add constraint FK_CLB_CO2_SANVD foreign key (MaSan)
      references SANVD (MaSan)
go

alter table CLB
   add constraint FK_CLB_QUANLY_BTC foreign key (Ma_BTC)
      references BTC (Ma_BTC)
go

alter table CLB
   add constraint FK_CLB_TAITRO_CL_NTT foreign key (Ma_NTT)
      references NTT (Ma_NTT)
go

alter table CO3
   add constraint FK_CO3_CO3_CLB foreign key (Ma_CLB)
      references CLB (Ma_CLB)
go

alter table CO3
   add constraint FK_CO3_CO4_TONGKET foreign key (MaDH)
      references TONGKET (MaDH)
go

alter table DIEUKHIEN
   add constraint FK_DIEUKHIE_DIEUKHIEN_TRONGTAI foreign key (Ma_TT)
      references TRONGTAI (Ma_TT)
go

alter table DIEUKHIEN
   add constraint FK_DIEUKHIE_DIEUKHIEN_CAPDAU foreign key (Ma_CD, ThoiGian)
      references CAPDAU (Ma_CD, ThoiGian)
go

alter table THIDAU
   add constraint FK_THIDAU_THIDAU_CAPDAU foreign key (Ma_CD, ThoiGian)
      references CAPDAU (Ma_CD, ThoiGian)
go

alter table THIDAU
   add constraint FK_THIDAU_THIDAU2_CLB foreign key (Ma_CLB)
      references CLB (Ma_CLB)
go

alter table TRONGTAI
   add constraint FK_TRONGTAI_PHANCONG_BTC foreign key (Ma_BTC)
      references BTC (Ma_BTC)
go

alter table TRONGTAI
   add constraint FK_TRONGTAI_THUOC3_VT_TT foreign key (Ma_VTTT)
      references VT_TT (Ma_VTTT)
go

alter table VONGDAU
   add constraint FK_VONGDAU_SAPXEP_BTC foreign key (Ma_BTC)
      references BTC (Ma_BTC)
go

